import numpy as np
import pandas as pd

import PathUtil


def has_different_pon(group):
    result = []
    for i, row in group.iterrows():
        time = row['故障发生时间']
        start_time = time - pd.Timedelta(minutes=10)
        end_time = time + pd.Timedelta(minutes=10)
        # 筛选出同一 ip 在十分钟时间窗口内的数据
        window = group[(group['故障发生时间'] >= start_time) & (group['故障发生时间'] < end_time)]
        # 判断 pon 是否有不同的值
        result.append(len(window[port_col].unique()) > 1)
    return pd.Series(result, index=group.index)


if __name__ == '__main__':
    ip_col = '传输设备ip'
    port_col = '传输设备端口2'

    file_paths = [
        r"D:\Download\WeChat Files\wxid_kdchbeq2xllp22\FileStorage\File\2025-06\1751277268498.xlsx"
    ]
    dfs = []
    for file_path in file_paths:
        df = pd.read_excel(file_path)
        required_columns = ['流水号', '工单状态',
                            '告警名称', '告警描述', '故障发生时间', ip_col, '传输设备端口']
        missing_columns = [col for col in required_columns if col not in df.columns]
        if missing_columns:
            print(f'文件 {file_path} 缺少以下列: {missing_columns}')
            continue
        df = df[required_columns]
        dfs.append(df)
    df = pd.concat(dfs, ignore_index=True)
    df['故障发生时间'] = pd.to_datetime(df['故障发生时间'])
    # start_date = pd.to_datetime('2025-04-26')
    # end_date = pd.to_datetime('2025-05-17')
    # df = df[(df['故障发生时间'] >= start_date) & (df['故障发生时间'] <= end_date)]
    df = df[df['告警名称'] != '告警名称']
    # df = df.drop_duplicates()

    t = pd.read_csv(PathUtil.olt_bras(), usecols=['OLT_IP', 'BRAS1_ip'])
    t.rename(columns={
        'OLT_IP': ip_col
    }, inplace=True)
    df = df.merge(t, on=ip_col, how='left')

    types = pd.read_csv(
        "D:\\家宽\\source\\t_rules_alarm_category.csv")

    types['告警名'] = types['告警名'].str.replace(r'\(\d+\)', '', regex=True)
    uplink = types[types['分段'] == '上联']['告警名']
    tuifu = types[types['类型'] == 'OLT']['告警名']
    gj_condition = pd.concat([uplink, tuifu])
    is_uplink_tuifu = df['告警名称'].isin(gj_condition)


    def calculate_oltip_count(df: pd.DataFrame, alert_name_series: pd.Series) -> pd.DataFrame:
        filtered_df = df[df['告警名称'].isin(alert_name_series)]

        def count_oltip_within_time(group):
            def count_oltip(row):
                start_time = row['故障发生时间'] - pd.Timedelta(minutes=10)
                end_time = row['故障发生时间'] + pd.Timedelta(minutes=10)
                sub_df = group[(group['故障发生时间'] >= start_time) & (group['故障发生时间'] <= end_time)]
                return sub_df[ip_col].nunique()

            return group.apply(count_oltip, axis=1)

        oltip_counts = filtered_df.groupby('BRAS1_ip').apply(count_oltip_within_time).reset_index(0)
        # 根据索引进行合并操作
        df['unique_count_10min'] = oltip_counts[0]
        return df


    df = calculate_oltip_count(df, gj_condition)

    conditions = [
        df['unique_count_10min'] == 1,
        (df['unique_count_10min'] >= 2) & (df['unique_count_10min'] <= 10),
        df['unique_count_10min'] > 10
    ]
    choices = ['单olt', '多olt', '单bras']
    df['33'] = np.select(conditions, choices, default=df['unique_count_10min'])

    df.to_excel('/temp/t.xlsx')
